package org.dylan.common.dao.jdbc;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javacommon.xsqlbuilder.SafeSqlProcesserFactory;
import javacommon.xsqlbuilder.XsqlBuilder;
import javacommon.xsqlbuilder.XsqlBuilder.XsqlFilterResult;
import javacommon.xsqlbuilder.safesql.DirectReturnSafeSqlProcesser;

import org.dylan.common.dao.jdbc.support.OffsetLimitResultSetExtractor;
import org.dylan.common.model.QueryRequest;
import org.dylan.common.paging.PageData;
import org.dylan.common.paging.PageRequest;
import org.dylan.common.paging.dialect.PageDialect;
import org.dylan.common.util.XSqlUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

/**
 * Spring JDBC方式的数据查询
 * 
 * @author dylan
 * @date 2013-6-4
 */
@SuppressWarnings("all")
public class SpringJdbcDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedJdbcTemplate;
	// 用于分页的dialect
	private PageDialect pageDialect;

	/**
	 * 从全部数据中查询所需的数据，将会忽略请求的参数
	 * 
	 * @param tableName
	 * @param queryRequest
	 * @return
	 */
	public <T> List<T> findAll(String tableName, QueryRequest queryRequest) {
		String xsql = "select * from " + tableName + " /~ order by ["
				+ XSqlUtils.SORT_COLUMNS_KEY + "] ~/";
		return findByRequest(xsql, queryRequest);
	}

	/**
	 * 给定xsql语句和查询参数，检索数据
	 * @param xsql
	 * @param queryRequest
	 * @return
	 */
	public List findByRequest(String xsql, QueryRequest queryRequest) {
		return findByRequest(xsql, queryRequest, new ColumnMapRowMapper());
	}
	/**
	 * 给定xsql语句和查询参数，检索数据，并将结果映射为mappedClass类型(使用{@link BeanPropertyRowMapper})
	 * @param xsql
	 * @param queryRequest
	 * @param mappedClass
	 * @return
	 */
	public List findByRequest(String xsql, QueryRequest queryRequest,
			Class mappedClass) {
		return findByRequest(xsql, queryRequest, new BeanPropertyRowMapper(
				mappedClass));
	}
	/**
	 * 给定xsql语句和查询参数，检索数据，结果通过rowMapper进行映射
	 * @param xsql
	 * @param queryRequest
	 * @param mappedClass
	 * @return
	 */
	public List findByRequest(String xsql, QueryRequest queryRequest,
			RowMapper rowMapper) {
		XsqlFilterResult filterResult = XSqlUtils.buildXsqlFilterResult(
				getXsqlBuilder(), xsql, queryRequest);
		return findByXsqlFilterRequest(filterResult, rowMapper);
	}

	private List findByXsqlFilterRequest(XsqlFilterResult filterResult,
			RowMapper rowMapper) {
		return namedJdbcTemplate.query(filterResult.getXsql(),
				filterResult.getAcceptedFilters(), rowMapper);
	}

	/**
	 * 根据xsql和请求数据进行数据的查询
	 * 
	 * @param xsql
	 * @param pageRequest
	 * @return
	 */
	public PageData pageQuery(String xsql, PageRequest pageRequest) {
		return pageQuery(xsql, pageRequest, new ColumnMapRowMapper());
	}

	/**
	 * 给定查询的xsql和记录统计的xsql，进行分页数据的检索
	 * 
	 * @param xsql
	 * @param countXSQL
	 * @param pageRequest
	 * @return
	 */
	public PageData pageQuery(String xsql, String countXSQL,
			PageRequest pageRequest) {
		return pageQuery(xsql, countXSQL, pageRequest, new ColumnMapRowMapper());
	}

	/**
	 * 给定查询的xsql，进行分页数据的检索,并将结果转换为对应的类模型(使用BeanPropertyRowMapper)
	 * 
	 * @param xsql
	 * @param pageRequest
	 * @param clazz
	 * @return
	 */
	public PageData pageQuery(String xsql, PageRequest pageRequest, Class clazz) {
		return pageQuery(xsql, pageRequest, new BeanPropertyRowMapper(clazz));
	}

	/**
	 * 给定查询的xsql和数据统计的xsql，进行分页数据的检索,并将结果转换为对应的类模型(使用BeanPropertyRowMapper)
	 * 
	 * @param xsql
	 * @param countXSQL
	 * @param pageRequest
	 * @param clazz
	 * @return
	 */
	public PageData pageQuery(String xsql, String countXSQL,
			PageRequest pageRequest, Class mappedClass) {
		return pageQuery(xsql, countXSQL, pageRequest,
				new BeanPropertyRowMapper(mappedClass));
	}

	/**
	 * 给定查询的xsql，进行分页数据的检索
	 * 
	 * @param xsql
	 * @param pageRequest
	 * @param rowMapper
	 * @return
	 */
	public PageData pageQuery(String xsql, PageRequest pageRequest,
			RowMapper rowMapper) {
		String countSQL = "select count(*) from "
				+ XSqlUtils.removeOrders(XSqlUtils.removeSelect(xsql));
		return pageQuery(xsql, countSQL, pageRequest, rowMapper);
	}

	/**
	 * 给定查询的xsql和数据统计的xsql，进行分页数据的检索
	 * 
	 * @param xsql
	 * @param countQuerySQL
	 * @param pageRequest
	 * @param rowMapper
	 * @return
	 */
	public PageData pageQuery(String xsql, String countQuerySQL,
			final PageRequest pageRequest, RowMapper rowMapper) {
		if (pageRequest.unLimit()) {
			PageData pageData = new PageData(pageRequest.limit());
			List datas = findByRequest(xsql, pageRequest, rowMapper);
			pageData.setTotalCount(datas == null ? 0 : datas.size());
			pageData.setResult(datas);
			return pageData;
		}
		int totalCount = queryTotalCount(countQuerySQL, pageRequest);
		PageData pageData = new PageData(pageRequest.limit());
		pageData.setTotalCount(0);
		if( totalCount == 0){
			return pageData;
		}
		
		XsqlFilterResult queryXsqlResult = XSqlUtils.buildXsqlFilterResult(
				getXsqlBuilder(), xsql, pageRequest);
		String sql = queryXsqlResult.getXsql();
		
		List list = pageQuery(sql, queryXsqlResult.getAcceptedFilters(), pageRequest.start(), pageRequest.limit(), rowMapper);
		pageData.setResult(list);
		return pageData;
	}

	protected XsqlBuilder getXsqlBuilder() {
		XsqlBuilder builder = new XsqlBuilder(
				SafeSqlProcesserFactory.getMsSqlServer());
		if (builder.getSafeSqlProcesser().getClass() == DirectReturnSafeSqlProcesser.class) {
			System.err
					.println("BaseSpringJdbcDao.getXsqlBuilder(): 故意报错,你未开启Sql安全过滤,单引号等转义字符在拼接sql时需要转义,不然会导致Sql注入攻击的安全问题，请修改源码使用new XsqlBuilder(SafeSqlProcesserFactory.getDataBaseName())开启安全过滤");
		}
		return builder;
	}

	private int queryTotalCount(String countSql, PageRequest pageRequest) {
		XsqlFilterResult countQueryXsqlResult = XSqlUtils
				.buildXsqlFilterResult(getXsqlBuilder(), countSql, pageRequest);
		String removedOrderByQuery = XSqlUtils
				.removeOrders(countQueryXsqlResult.getXsql());
		int totalCount = namedJdbcTemplate.queryForObject(removedOrderByQuery,
				countQueryXsqlResult.getAcceptedFilters(), Number.class)
				.intValue();
		return totalCount;
	}

	protected List pageQuery(String sql, final Map<String, Object> pramas,
			int startRow, int pageSize, final RowMapper rowMapper) {
		// 支持limit及offset.则完全使用数据库分页
		if (pageDialect.supportsLimitOffset()) {
			sql = pageDialect.getLimitString(sql, startRow, pageSize);
			return namedJdbcTemplate.query(sql, pramas, rowMapper);
		}
		// 支持limit查询
		if (pageDialect.supportsLimit()) {
			// 不支持offset,则在后面查询中使用游标配合limit分页
			sql = pageDialect.getLimitString(sql, startRow, pageSize);
		}
		// 不支持数据分页
		List result = namedJdbcTemplate
				.query(sql, pramas, new OffsetLimitResultSetExtractor(startRow,
						pageSize, rowMapper));
		if (result != null && result.size() > pageSize) {
			result = result.subList(startRow, result.size());
		}
		return result;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
		return namedJdbcTemplate;
	}

	public void setNamedJdbcTemplate(
			NamedParameterJdbcTemplate namedJdbcTemplate) {
		this.namedJdbcTemplate = namedJdbcTemplate;
	}

	public PageDialect getPageDialect() {
		return pageDialect;
	}

	public void setPageDialect(PageDialect pageDialect) {
		this.pageDialect = pageDialect;
	}

}
